Table: Logs
| Column Name | Type |
|---|---|
| id | int |
| num | varchar |
In SQL, id is the primary key for this table.
id is an autoincrement column.
Find all numbers that appear at least three times consecutively.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input:
Logs table:
| id | num |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
Output:
| ConsecutiveNums |
|---|
| 1 |
Explanation: 1 is the only number that appears consecutively for at least three times.
SELECT l1.num AS ConsecutiveNums
FROM Logs l1
LEFT JOIN Logs l2
ON (L1.id+1) = l2.id
LEFT JOIN Logs l3
ON (L1.id+2) = l3.id
WHERE l1.num = l2.num AND l2.num = l3.num
GROUP BY l1.num
解題思路：
先把自己join起來，在把l1.num = l2.num AND l2.num = l3.num取出來
SELECT *
FROM Logs l1
LEFT JOIN Logs l2
ON (L1.id+1) = l2.id
LEFT JOIN Logs l3
ON (L1.id+2) = l3.id
| id | num | id | num | id | num |
|---|---|---|---|---|---|
| 1 | 1 | 2 | 1 | 3 | 1 |
| 2 | 1 | 3 | 1 | 4 | 2 |
| 3 | 1 | 4 | 2 | 5 | 1 |
| 4 | 2 | 5 | 1 | 6 | 2 |
| 5 | 1 | 6 | 2 | 7 | 2 |
| 6 | 2 | 7 | 2 | null | null |
| 7 | 2 | null | null | null | null |
